package com.hsnn.medstgmini.pro.project.dao.impl;

import java.math.BigInteger;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.UUID;

import javax.annotation.Resource;

import org.apache.commons.lang.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.hsnn.medstgmini.base.std.model.StdCompinfo;
import com.hsnn.medstgmini.base.std.model.form.StdProdDrugInfoForm;
import com.hsnn.medstgmini.dao.HibernateSqlUtil;
import com.hsnn.medstgmini.pro.project.dao.ProdDrugDao;
import com.hsnn.medstgmini.pro.project.model.form.ProdProjectForm;
import com.hsnn.medstgmini.sys.model.SysUser;
import com.hsnn.medstgmini.util.MapToBeanUtil;
import com.hsnn.medstgmini.util.Pagination;

/**
 * @category 生产企业药品申报管理
 * @author 蔡春龙
 * @date 2015年8月28日
 */
@Repository
public class ProdDrugDaoImpl implements ProdDrugDao {

	@Resource(name = "sessionFactory")
	private SessionFactory factory;

	@Autowired
	private HibernateSqlUtil sqlUtil;

	/**
	 * @category 分页查询某企业可申报的药品
	 * @date 2015年8月30日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getProdDrugList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT e.proj_id, a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form, b.spec, a.factor, a.unit, d.material_id materialId, d.material_name materialName, a.prod_comp_id prodCompId, c.comp_name prodCompName, a.status FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id AND e.proj_id = :projId WHERE 1 = 1 AND a.status = 1 and a.audit_status=1 AND a.prod_comp_id = :compId AND NOT EXISTS ( SELECT * FROM bid_projproddrug f WHERE a.prod_id = f.prod_id AND f.proj_id = :projId ) ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", form.getProjId());
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				sb.append(" AND c.comp_name LIKE :prodCompName ");
				params.put("prodCompName", "%" + prodCompName + "%");
			}
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}

	/**
	 * @category 提交申报药品
	 * @date 2015年8月30日
	 * @param list
	 * @param user
	 */
	public void submitProdDrugList(List<StdProdDrugInfoForm> list, SysUser user) {
		Session session = factory.getCurrentSession();
		if (list != null && !list.isEmpty()) {
			List<String> ids = new LinkedList<String>();
			for (StdProdDrugInfoForm form : list) {
				String projId = form.getProjId();
				String prodId = form.getProdId();

				String sql = " SELECT COUNT(a.proj_proddrug_id) FROM bid_projproddrug a WHERE a.proj_id = :projId AND a.prod_id = :prodId ";
				SQLQuery query = session.createSQLQuery(sql);
				query.setString("projId", projId);
				query.setString("prodId", prodId);
				Long count = ((BigInteger) query.uniqueResult()).longValue();

				if (count == 0) {
					String id = UUID.randomUUID().toString();
					ids.add(id);
					sql = " insert into bid_projproddrug (proj_proddrug_id, proj_id, prod_id, status, audit_status, complain_status, upd_user, upd_datetime) values (:id, :projId, :prodId, 1, 0, 3, :updUser, sysdate()) ";
					query = session.createSQLQuery(sql);
					query.setString("id", id);
					query.setString("projId", projId);
					query.setString("prodId", prodId);
					query.setString("updUser", user.getId());
					query.executeUpdate();
				}
			}
			session.flush();

			saveSubmitLog(ids);
		}
	}

	/**
	 * @category 保存提交日志
	 * @date 2015年8月30日
	 * @param ids
	 */
	private void saveSubmitLog(List<String> ids) {
		if (ids != null && !ids.isEmpty()) {
			Session session = factory.getCurrentSession();
			String sql = " insert into bid_projproddrug_log select uuid() log_id, a.* from bid_projproddrug a where a.proj_proddrug_id in (:ids) ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setParameterList("ids", ids);
			query.executeUpdate();
			session.flush();
		}

	}

	/**
	 * @category 查询企业申报药品审核结果列表
	 * @date 2015年8月30日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugAuditResultList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT e.proj_id, a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form, b.spec, a.factor, a.unit, d.material_id materialId, d.material_name materialName, a.prod_comp_id prodCompId, c.comp_name prodCompName, a.status, f.ref_price refPrice, f.dir_price dirPrice, f.is_bus_scope isBusScope, f.is_semi_bidding isSemiBidding, f.is_bidding isBidding, f.audit_status auditStatus, f.audit_remark auditRemark, DATE_FORMAT(f.audit_datetime, '%Y-%m-%d') auditDatetime, f.proj_proddrug_id projProdDrugId FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id AND e.proj_id = :projId JOIN bid_projproddrug f ON a.prod_id = f.prod_id AND f.proj_id = :projId WHERE 1 = 1 AND a.status = 1 AND a.prod_comp_id = :compId ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", form.getProjId());
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				sb.append(" AND c.comp_name LIKE :prodCompName ");
				params.put("prodCompName", "%" + prodCompName + "%");
			}
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}

	/**
	 * @category 企业产品澄清
	 * @date 2015年9月1日
	 * @param form
	 * @param user
	 */
	public void complainProdDrug(StdProdDrugInfoForm form, SysUser user) {
		String projProdDrugId = form.getProjProdDrugId();
		if (StringUtils.isNotBlank(projProdDrugId)) {
			Session session = factory.getCurrentSession();
			String sql = " update bid_projproddrug set complain_status = 0, complain_time = sysdate(), complain_remark = :complainRemark, upd_datetime = sysdate(), upd_user = :updUser where proj_proddrug_id = :projProdDrugId ";
			SQLQuery query = session.createSQLQuery(sql);
			query.setString("complainRemark", form.getComplainRemark());
			query.setString("projProdDrugId", projProdDrugId);
			query.setString("updUser", form.getUpdUser());
			query.executeUpdate();
			session.flush();

			// 记录日志
			List<String> ids = new LinkedList<String>();
			ids.add(projProdDrugId);
			saveSubmitLog(ids);
		}
	}
	
	/**
	 * @category 查询企业产品澄清列表
	 * @date 2015年9月2日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugComplainList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT e.proj_id, a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form, b.spec, a.factor, a.unit, d.material_id materialId, d.material_name materialName, a.prod_comp_id prodCompId, c.comp_name prodCompName, a.status, f.ref_price refPrice, f.dir_price dirPrice, f.is_bus_scope isBusScope, f.is_semi_bidding isSemiBidding, f.is_bidding isBidding, f.audit_status auditStatus, f.audit_remark auditRemark, DATE_FORMAT( f.audit_datetime, '%Y-%m-%d' ) auditDatetime, f.proj_proddrug_id projProdDrugId, f.complain_audit_status complainAuditStatus, DATE_FORMAT( f.complain_audit_datetime, '%Y-%m-%d' ) complainAuditDatetime FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id AND e.proj_id = :projId JOIN bid_projproddrug f ON a.prod_id = f.prod_id AND f.proj_id = :projId WHERE 1 = 1 AND a.status = 1 AND a.prod_comp_id = :compId AND f.audit_status = 2 AND f.complain_status = 3 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", form.getProjId());
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				sb.append(" AND c.comp_name LIKE :prodCompName ");
				params.put("prodCompName", "%" + prodCompName + "%");
			}
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}

	/**
	 * @category 企业产品澄清结果列表查询
	 * @date 2015年9月2日
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugComplainResultList(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT e.proj_id, a.prod_id prodId, a.qualityspec_id qualityspecId, a.drug_code drugCode, b.qualityspec_name qualityspecName, b.form, b.spec, a.factor, a.unit, d.material_id materialId, d.material_name materialName, a.prod_comp_id prodCompId, c.comp_name prodCompName, a.status, f.ref_price refPrice, f.dir_price dirPrice, f.is_bus_scope isBusScope, f.is_semi_bidding isSemiBidding, f.is_bidding isBidding, f.audit_status auditStatus, f.audit_remark auditRemark, DATE_FORMAT( f.audit_datetime, '%Y-%m-%d' ) auditDatetime, f.proj_proddrug_id projProdDrugId, f.complain_audit_status complainAuditStatus, DATE_FORMAT( f.complain_audit_datetime, '%Y-%m-%d' ) complainAuditDatetime FROM std_proddruginfo a JOIN std_drugqualityspec b ON a.qualityspec_id = b.qualityspec_id LEFT JOIN std_compinfo c ON a.prod_comp_id = c.comp_id LEFT JOIN dic_drugmaterial d ON a.material_id = d.material_id JOIN bid_qualityspec e ON a.qualityspec_id = e.qualityspec_id AND e.proj_id = :projId JOIN bid_projproddrug f ON a.prod_id = f.prod_id AND f.proj_id = :projId WHERE 1 = 1 AND a.status = 1 AND a.prod_comp_id = :compId AND f.audit_status = 2 AND f.complain_status <> 3 ");

		// 查询条件
		StdProdDrugInfoForm form = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", form.getProjId());
		params.put("compId", form.getProdCompId());

		if (form != null) {
			// 药品编码
			String drugCode = form.getDrugCode();
			if (StringUtils.isNotBlank(drugCode)) {
				sb.append(" AND a.drug_code = :drugCode ");
				params.put("drugCode", drugCode);
			}

			// 通用名
			String qualityspecName = form.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND b.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			// 生产企业名称
			String prodCompName = form.getProdCompName();
			if (StringUtils.isNotBlank(prodCompName)) {
				sb.append(" AND c.comp_name LIKE :prodCompName ");
				params.put("prodCompName", "%" + prodCompName + "%");
			}
		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}
	
	/**
	 * @category 查询项目列表
	 * @date 2015年9月7日
	 * @param page
	 * @param user
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugProjectListDeclare(Pagination page, SysUser user) {

		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.proj_id AS projId, MAX(a.name) name, COUNT(*) totalCount, count(d.proj_proddrug_id) declaredCount, DATE_FORMAT(MAX(h.pro_submit_starttime),'%Y-%m-%d') proSubmitStarttime, DATE_FORMAT(MAX(h.pro_submit_endtime),'%Y-%m-%d') proSubmitEndtime FROM bid_project a JOIN bid_projprodcompany b   ON a.proj_id = b.proj_id left join bid_project_details h  ON b.proj_id = h.proj_id  JOIN std_proddruginfo c  ON b.prod_comp_id = c.prod_comp_id  AND c.status = 1   AND c.audit_status=1  join bid_qualityspec bq on bq.qualityspec_id = c.qualityspec_id and bq.proj_id= b.proj_id LEFT JOIN bid_projproddrug d   ON  b.proj_id = d.proj_id and c.prod_id = d.prod_id WHERE  a.proj_cat = 1  AND a.status = 1  AND a.starttime <= SYSDATE()  AND A.endtime >= SYSDATE()  AND b.prod_comp_id =:compId ");

		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);

		if (form != null) {
			String projId = form.getProjId();
			if (StringUtils.isNotBlank(projId)) {
				sb.append(" AND a.proj_id = :projId ");
				params.put("projId", projId);
			}
			String name = form.getName();
			if (StringUtils.isNotBlank(name)) {
				sb.append(" AND a.proj_id = :name ");
				params.put("name", name );
			}
		}
		sb.append(" GROUP BY a.proj_id order by a.create_datetime desc ");
		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();

		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,
				ProdProjectForm.class);

		page.setRows(projList);

		return page;
	
	}
	
	/**
	 * @category 查询项目列表
	 * @date 2015年9月7日
	 * @param page
	 * @param user
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugProjectListDeclareSee(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.proj_id AS projId, MAX(a.proj_code) AS projCode, MAX(a.name) name, MAX(a.proj_cat) AS projCat, MAX(a.status) status, DATE_FORMAT( MAX(a.create_datetime), '%Y-%m-%d' ) AS createDatetime, DATE_FORMAT( MAX(a.starttime), '%Y-%m-%d' ) AS starttime, DATE_FORMAT(MAX(a.endtime), '%Y-%m-%d') AS endtime, MAX(a.proj_intro) AS projIntro, MAX(a.remark) remark, COUNT(c.prod_id) totalCount, SUM(CASE WHEN d.audit_status = 1 THEN 1 ELSE 0 END) approveCount, SUM(CASE WHEN d.audit_status = 2 THEN 1 ELSE 0 END) unapproveCount, DATE_FORMAT(MAX(e.pro_submit_starttime), '%Y-%m-%d') AS proSubmitStarttime, DATE_FORMAT(MAX(e.pro_submit_endtime), '%Y-%m-%d') AS proSubmitEndtime, DATE_FORMAT(MAX(e.pro_audit_pub_starttime), '%Y-%m-%d') AS proAuditPubStarttime, DATE_FORMAT(MAX(e.pro_audit_pub_endtime), '%Y-%m-%d') AS proAuditPubEndtime, DATE_FORMAT(MAX(e.complain_starttime), '%Y-%m-%d') AS complainStarttime, DATE_FORMAT(MAX(e.complain_endtime), '%Y-%m-%d') AS complainEndtime, DATE_FORMAT(MAX(e.complain_pub_starttime), '%Y-%m-%d') AS complainPubStarttime, DATE_FORMAT(MAX(e.complain_pub_endtime), '%Y-%m-%d') AS complainPubEndtime FROM view_bidproject a JOIN bid_projprodcompany b ON a.proj_id = b.proj_id JOIN std_proddruginfo c ON b.prod_comp_id = c.prod_comp_id AND c.status = 1 JOIN bid_projproddrug d ON a.proj_id = d.proj_id AND c.prod_id = d.prod_id LEFT JOIN bid_project_details e ON a.proj_id = e.proj_id WHERE 1 = 1 AND a.proj_cat = 1 AND a.status = 1 AND a.starttime <= SYSDATE() AND A.endtime >= SYSDATE() AND b.prod_comp_id = :compId GROUP BY a.proj_id ");

		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);

		if (form != null) {
			String projCode = form.getProjCode();
			if (StringUtils.isNotBlank(projCode)) {
				sb.append(" AND a.proj_code = :projCode ");
				params.put("projCode", projCode);
			}
			String name = form.getName();
			if (StringUtils.isNotBlank(name)) {
				sb.append(" AND a.name LIKE :name ");
				params.put("name", "%" + name + "%");
			}
		}

		sb.append(" order by a.create_datetime desc ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();

		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,
				ProdProjectForm.class);

		page.setRows(projList);

		return page;
	}
	
	/**
	 * @category 查询项目列表
	 * @date 2015年9月7日
	 * @param page
	 * @param user
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugProjectListClarify(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.proj_id AS projId, MAX(a.proj_code) AS projCode, MAX(a.name) name, MAX(a.proj_cat) AS projCat, MAX(a.status) status, DATE_FORMAT( MAX(a.create_datetime), '%Y-%m-%d' ) AS createDatetime, DATE_FORMAT( MAX(a.starttime), '%Y-%m-%d' ) AS starttime, DATE_FORMAT(MAX(a.endtime), '%Y-%m-%d') AS endtime, MAX(a.proj_intro) AS projIntro, MAX(a.remark) remark, COUNT(c.prod_id) totalCount, SUM(CASE WHEN d.complain_status = 3 THEN 1 ELSE 0 END) unsettleCount, DATE_FORMAT(MAX(e.pro_submit_starttime), '%Y-%m-%d') AS proSubmitStarttime, DATE_FORMAT(MAX(e.pro_submit_endtime), '%Y-%m-%d') AS proSubmitEndtime, DATE_FORMAT(MAX(e.pro_audit_pub_starttime), '%Y-%m-%d') AS proAuditPubStarttime, DATE_FORMAT(MAX(e.pro_audit_pub_endtime), '%Y-%m-%d') AS proAuditPubEndtime, DATE_FORMAT(MAX(e.complain_starttime), '%Y-%m-%d') AS complainStarttime, DATE_FORMAT(MAX(e.complain_endtime), '%Y-%m-%d') AS complainEndtime, DATE_FORMAT(MAX(e.complain_pub_starttime), '%Y-%m-%d') AS complainPubStarttime, DATE_FORMAT(MAX(e.complain_pub_endtime), '%Y-%m-%d') AS complainPubEndtime FROM view_bidproject a JOIN bid_projprodcompany b ON a.proj_id = b.proj_id JOIN std_proddruginfo c ON b.prod_comp_id = c.prod_comp_id AND c.status = 1 JOIN bid_projproddrug d ON a.proj_id = d.proj_id AND c.prod_id = d.prod_id LEFT JOIN bid_project_details e ON a.proj_id = e.proj_id WHERE 1 = 1 AND a.proj_cat = 1 AND a.status = 1 AND a.starttime <= SYSDATE() AND A.endtime >= SYSDATE() AND d.audit_status = 2 AND b.prod_comp_id = :compId GROUP BY a.proj_id ");

		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);

		if (form != null) {
			String projCode = form.getProjCode();
			if (StringUtils.isNotBlank(projCode)) {
				sb.append(" AND a.proj_code = :projCode ");
				params.put("projCode", projCode);
			}
			String name = form.getName();
			if (StringUtils.isNotBlank(name)) {
				sb.append(" AND a.name LIKE :name ");
				params.put("name", "%" + name + "%");
			}
		}

		sb.append(" order by a.create_datetime desc ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();

		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,
				ProdProjectForm.class);

		page.setRows(projList);

		return page;
	}
	
	/**
	 * @category 查询项目列表
	 * @date 2015年9月7日
	 * @param page
	 * @param user
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugProjectListClarifySee(Pagination page, SysUser user) {
		StdCompinfo compInfo = user.getInfo();

		if (compInfo == null) {
			return page;
		}

		String compId = compInfo.getCompId();

		StringBuffer sb = new StringBuffer();
		sb.append(" SELECT a.proj_id AS projId, MAX(a.proj_code) AS projCode, MAX(a.name) name, MAX(a.proj_cat) AS projCat, MAX(a.status) status, DATE_FORMAT( MAX(a.create_datetime), '%Y-%m-%d' ) AS createDatetime, DATE_FORMAT( MAX(a.starttime), '%Y-%m-%d' ) AS starttime, DATE_FORMAT(MAX(a.endtime), '%Y-%m-%d') AS endtime, MAX(a.proj_intro) AS projIntro, MAX(a.remark) remark, COUNT(c.prod_id) totalCount, SUM(CASE WHEN d.complain_status = 1 THEN 1 ELSE 0 END) settleApproveCount, SUM(CASE WHEN d.complain_status = 2 THEN 1 ELSE 0 END) settleUnsettleCount, DATE_FORMAT(MAX(e.pro_submit_starttime), '%Y-%m-%d') AS proSubmitStarttime, DATE_FORMAT(MAX(e.pro_submit_endtime), '%Y-%m-%d') AS proSubmitEndtime, DATE_FORMAT(MAX(e.pro_audit_pub_starttime), '%Y-%m-%d') AS proAuditPubStarttime, DATE_FORMAT(MAX(e.pro_audit_pub_endtime), '%Y-%m-%d') AS proAuditPubEndtime, DATE_FORMAT(MAX(e.complain_starttime), '%Y-%m-%d') AS complainStarttime, DATE_FORMAT(MAX(e.complain_endtime), '%Y-%m-%d') AS complainEndtime, DATE_FORMAT(MAX(e.complain_pub_starttime), '%Y-%m-%d') AS complainPubStarttime, DATE_FORMAT(MAX(e.complain_pub_endtime), '%Y-%m-%d') AS complainPubEndtime FROM view_bidproject a JOIN bid_projprodcompany b ON a.proj_id = b.proj_id JOIN std_proddruginfo c ON b.prod_comp_id = c.prod_comp_id AND c.status = 1 JOIN bid_projproddrug d ON a.proj_id = d.proj_id AND c.prod_id = d.prod_id LEFT JOIN bid_project_details e ON a.proj_id = e.proj_id WHERE 1 = 1 AND a.proj_cat = 1 AND a.status = 1 AND a.starttime <= SYSDATE() AND A.endtime >= SYSDATE() AND d.audit_status = 2 AND (d.complain_status = 0 OR d.complain_status = 1 OR d.complain_status = 2) AND b.prod_comp_id = :compId GROUP BY a.proj_id ");

		ProdProjectForm form = (ProdProjectForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("compId", compId);

		if (form != null) {
			String projCode = form.getProjCode();
			if (StringUtils.isNotBlank(projCode)) {
				sb.append(" AND a.proj_code = :projCode ");
				params.put("projCode", projCode);
			}
			String name = form.getName();
			if (StringUtils.isNotBlank(name)) {
				sb.append(" AND a.name LIKE :name ");
				params.put("name", "%" + name + "%");
			}
		}

		sb.append(" order by a.create_datetime desc ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();

		List<ProdProjectForm> projList = MapToBeanUtil.mapToBean(list,
				ProdProjectForm.class);

		page.setRows(projList);

		return page;
	}

	/**
	 * 
	 * @category 分页查询品规列表，用于新增通用名、通用名英文、剂型、规格
	 * @author 言科
	 * @date 2015年8月19日
	 */
	@SuppressWarnings("unchecked")
	public Pagination getDrugSelectQualityspec(Pagination page) {
		StringBuffer sb = new StringBuffer();
		sb.append(" select a.qualityspec_id  qualityspecId,a.qualityspec_code qualityspecCode, a.qualityspec_name  qualityspecName, b.proj_id projId, ");
		sb.append(" a.qualityspec_name_spel qualityspecNameSpel,a.qualityspec_name_eng qualityspecNameEng,a.form form,a.spec spec,a.status status ");
		sb.append(" FROM std_drugqualityspec a  ");
		sb.append(" join bid_qualityspec b on a.qualityspec_id = b.qualityspec_id ");
		sb.append(" where a.status = '1' and 1=1 and a.qualityspec_id = b.qualityspec_id and b.proj_id = :projId ");

		
		
		
		// 查询条件
		StdProdDrugInfoForm drugInfoForm = (StdProdDrugInfoForm) page.getForm();
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("projId", drugInfoForm.getProjId());
		if (drugInfoForm != null) {
			// 品规编码
			String qualityspecCode = drugInfoForm.getQualityspecCode();
			if (StringUtils.isNotBlank(qualityspecCode)) {
				sb.append(" AND a.qualityspec_code = :qualityspecCode ");
				params.put("qualityspecCode", qualityspecCode);
			}

			// 通用名
			String qualityspecName = drugInfoForm.getQualityspecName();
			if (StringUtils.isNotBlank(qualityspecName)) {
				sb.append(" AND a.qualityspec_name LIKE :qualityspecName ");
				params.put("qualityspecName", "%" + qualityspecName + "%");
			}

			// 剂型
			String form = drugInfoForm.getForm();
			if (StringUtils.isNotBlank(form)) {
				sb.append(" AND a.form LIKE :form ");
				params.put("form", "%" + form + "%");
			}

			// 规格
			String spec = drugInfoForm.getSpec();
			if (StringUtils.isNotBlank(spec)) {
				sb.append(" AND a.spec LIKE :spec ");
				params.put("spec", "%" + spec + "%");
			}

		}

		sb.append(" ORDER BY a.upd_datetime DESC ");

		sqlUtil.getSqlPageMapList(sb.toString(), params, page);

		List<Map<String, Object>> list = (List<Map<String, Object>>) page
				.getRows();
		page.setRows(MapToBeanUtil.mapToBean(list, StdProdDrugInfoForm.class));

		return page;
	}

}
